<?php

/* ========================================================================= */
/* This file is part of the HypnoPHP Framework Library                       */
/* Copyright (c) 2009-2010, Radu Buzea                                       */
/* ========================================================================= */

/**
 @author Radu Buzea
 @copyright Copyright (c) 2009-2010, Radu Buzea (www.hypnosis.ro)
 @license GNU Lesser General Public License (www.gnu.org/copyleft/lesser.html)
 @package MySQL Module for HypnoPHP
*/

// ============================================================================
// Class: Module_MySQL_Table
// ============================================================================

class Module_MySQL_Table extends HBaseDbTable
{

	# ###########################################################################

	/**
	* Generate the inital SQL code
	*
	* @param string $condition_sql
	* @return bool
	*/
	public function initSQL($condition_sql = '')
	{
		$table_name = $this->table_name;
		$db_object = $this->database_object;
		$scope = '';
		if (!is_null($table_name) && trim($table_name) != '') {
			$schema = $this->schema;
			if (!is_null($schema) && trim($schema) != '') {
				$scope = trim($schema) . '.' . trim($table_name);
			} else {
				$scope = trim($table_name);
			}
		}
		if ($scope != '') {
			$scope = $db_object->escapeNames($scope);
			$fields = "*";
			if (is_array($this->__fields) && count($this->__fields) > 0) {
				$fields = "";
				foreach ($this->__fields as $field_name) {
					if ($fields != "") { $fields .= ", "; }
					$fields .= $db_object->escapeNames($field_name);
				}
			}
			$limit = '';
			if ($this->__limit_start < 0) { $this->__limit_start = 0; }
			if ($this->__limit_length > 0) {
				$limit = " LIMIT " . $this->__limit_start . "," . $this->__limit_length;
			} else {
				$limit = " LIMIT " . $this->__limit_start . "," . self::DEFAULT_TABLE_SELECT_LIMIT;
			}
			if (trim($condition_sql) != '') {
				$condition_sql = trim($condition_sql);
				$p = strstr(strtoupper($condition_sql), 'WHERE');
				if ($p === false || $p != 0) {
					$condition_sql = "WHERE " . $condition_sql;
				}
				$condition_sql = " " . $condition_sql . " ";
			}
			$this->__sql = "SELECT {$fields} FROM {$scope}{$condition_sql}{$limit}";
			return true;
		}
		return false;
	}

	# ###########################################################################

	/**
	* Get a list of colums (fields) with properties
	*
	* @return array
	*/
	public function get_cols_array()
	{
		$cols = $this->getCols();
		if (!is_array($cols) || count($cols) == 0) { return $cols; }
		$result = array();
		foreach ($cols as $col) {
			$result[$col->name] = array(
				'table' => $col->table,
				'def' => $col->def,
				'max_length' => $col->max_length,
				'not_null' => $col->not_null,
				'primary_key' => $col->primary_key,
				'multiple_key' => $col->multiple_key,
				'unique_key' => $col->unique_key,
				'numeric' => $col->numeric,
				'blob' => $col->blob,
				'type' => $col->type,
				'unsigned' => $col->unsigned,
				'zerofill' => $col->zerofill,
				'length' => $col->length,
				'flags' => $col->flags,
			);
		}
		$this->__cols_array = $result;
		return $result;
	}

	# ###########################################################################

	/**
	* List the database table fileds
	*
	* @return array (list of fields) or bool (false on error)
	*/
	public function list_cols()
	{
		if (!$this->isReady()) { return false; }
		$table_name = '';
		$schema = '';
		$db_object = $this->database_object;
		if (isset($this->table_name) && trim($this->table_name) != '') {
			$table_name = trim($this->table_name);
			$sql_table_name = $table_name;
			if (isset($this->schema) && trim($this->schema) != '') {
				$schema = trim($this->schema);
				$sql_table_name = $schema . '.' . $sql_table_name;
			}
			$from = array($sql_table_name);
		}
		if ($sql_table_name == '') { return false; }
		$sql = "SHOW COLUMNS FROM " . $db_object->escapeNames($sql_table_name);
		$db_result = $db_object->query($sql);
		if (!$db_result) { return false; }
		$cols = array();
		$check_field_name = (is_array($this->__fields) && count($this->__fields));
		if ($db_object->numRows($db_result) > 0) {
			$offset = 0;
			while ($row = $db_object->fetchAssoc($db_result)) {
				if (isset($row['Field']) && isset($row['Type'])) {
					$field_name = $row['Field'];
					if ($check_field_name && !in_array($field_name, $this->__fields)) { continue; }
					$field_type = $row['Type'];
					$field_length = 0;
					$unsigned = false;
					$zerofill = false;
					preg_match_all("/([a-zA-Z]+|[0-9]+)/", $field_type, $matches, PREG_PATTERN_ORDER);
					if (isset($matches[0]) && is_array($matches[0]) && count($matches[0]) > 0) {
						 $field_type = $matches[0][0];
						 $field_length = (isset($matches[0][1]) && is_numeric($matches[0][1])) ? $matches[0][1] : 0;
						 $unsigned = (isset($matches[0][2]) && strtolower($matches[0][2]) == 'unsigned');
						 $zerofill = (isset($matches[0][3]) && strtolower($matches[0][3]) == 'zerofill');
					}
					$field_options = array(
						'name' => $field_name,
						'table_name' => $table_name,
						'schema' => $schema,
						'type' => $field_type,
						'length' => $field_length,
						'unsigned' => $unsigned,
						'zerofill' => $zerofill,
					);
					foreach ($row as $row_key => $row_value) {
						switch ($row_key) {
							case 'Null':
								$field_options['null'] = (strcasecmp($row_value, 'YES') == 0);
								break;
							case 'Key':
								$field_options['primary_key'] = (strcasecmp($row_value, 'PRI') == 0);
								$field_options['multiple_key'] = (strcasecmp($row_value, 'MUL') == 0);
								$field_options['unique_key'] = (strcasecmp($row_value, 'UNI') == 0);
								break;
							case 'Default':
								$field_options['default'] = $row_value;
								break;
							case 'Extra':
								$field_options['auto_increment'] = (strstr(strtoupper($row_value), 'AUTO_INCREMENT') !== false);
								break;
						}
					}
					$cols[$field_name] = new Module_MySQL_Field($field_options);
				}
				$offset++;
			}
		}
		return $cols;
	}

	# ###########################################################################

	/**
	* Optimize the database table
	*
	* @return bool
	*/
	public function optimize()
	{
		if (!$this->isReady() || $this->table_name == '') { return false; }
		$db_object = $this->database_object;
		return $this->$db_object->query("OPTIMIZE TABLE " .
			$db_object->escapeNames($this->table_name));
	}

	# ###########################################################################

	/**
	* Delete (DROP) the database table
	*
	* @return bool
	*/
	public function drop()
	{
		if (!$this->isReady() || $this->table_name == '') { return false; }
		$db_object = $this->database_object;
		$sql = "DROP TABLE " . $db_object->escapeNames($this->table_name);
		$this->addError("Cannot execute the SQL code \"{$sql}\" - DROP command not allowed",
			E_USER_WARNING);
		return false;
		// DO NOT ALLOW THIS CODE
		return $db_object->query($sql);
	}

	# ###########################################################################

	/**
	* Clear (TRUNCATE) the database table
	*
	* @return bool
	*/
	public function clear()
	{
		if (!$this->isReady() || $this->table_name == '') { return false; }
		$db_object = $this->database_object;
		$sql = "TRUNCATE TABLE " . $db_object->escapeNames($this->table_name);
		$this->addError("Cannot execute the SQL code \"{$sql}\" - TRUNCATE command not allowed",
			E_USER_WARNING);
		return false;
		// DO NOT ALLOW THIS CODE
		return $db_object->query($sql);
	}

	# ###########################################################################

	/**
	* Find records in the database table
	*
	* @param mixed
	* @return array or null on error
	*/
	public function find()
	{
		$num_args = func_num_args();
		$cols = $this->getCols(false);
		switch ($num_args) {
			case 0:
				$this->addError("The <i>find</i> function was called without any parameters", E_USER_ERROR);
				return null;
			case 1:
				$arg = func_get_arg(0);
				if (!is_int($arg) || $arg < 0) {
					$this->addError("The <i>find</i> function was called with an invalid parameter", E_USER_ERROR);
					return null;
				}
				$field = '';
				foreach ($cols as $col) {
					if ($col->primary_key) {
						$field = $col->name;
						break;
					}
				}
				if ($field != '') {
					$db_object = $this->database_object;
					$condition_sql = "WHERE " . $db_object->escapeNames($field) . " = '$arg'";
					if ($this->initSQL($condition_sql)) {
						$this->contentQuery($this->__sql);
						return $this->getRows();
					}
				}
				return null;
				break;
			case 2:
				$arg1 = func_get_arg(0);
				$arg2 = func_get_arg(1);
				if (!is_string($arg1) || trim($arg1) == '') { return false; }
				$arg1 = trim($arg1);
				foreach ($cols as $col) {
					if ($col->name == $arg1) {
						$field = $col->name;
						break;
					}
				}
				if ($field != '') {
					$db_object = $this->database_object;
					$condition_sql = "WHERE " . $db_object->escapeNames($field) . " = '" .
						$db_object->escapeStr($arg2) . "'";
					if ($this->initSQL($condition_sql)) {
						$this->contentQuery($this->__sql);
						return $this->getRows();
					}
				}
				return null;
				break;
			default:
				$this->addError("The <i>find</i> function was called with too many parameter", E_USER_ERROR);
				return null;
		}
	}

	# ###########################################################################

	/**
	* Update existing data from the database table
	*
	* @param array $fields A list of fields and values (field names as array keys)
	* @param mixed $where Update SQL condition (can be integer - update by primary key,
	* array - a list of conditions with filed names as array keys,
	* or string - plain SQL conditions)
	* @param integer $limit
	* @return resource (MySQL) or false on errors
	*/
	public function update(array $fields_data, $where, $limit = null)
	{
		if (!is_array($fields_data) || is_null($where) || count($fields_data) == 0) { return false; }

		$cols = $this->getCols(false);
		$db_object = $this->database_object;
		$table_name = $this->table_name;
		$schema = $this->schema;
		if (!is_null($table_name) && trim($table_name) != '') {
			$schema = $this->schema;
			if (!is_null($schema) && trim($schema) != '') {
				$scope = trim($schema) . '.' . trim($table_name);
			} else {
				$scope = trim($table_name);
			}
		}
		$scope = $db_object->escapeNames($scope);

		$where_sql = '';
		switch (gettype($where)) {
			case 'integer':
				foreach ($cols as $col) {
					if ($col->primary_key) {
						$where_sql = $db_object->escapeNames($col->name) . " = '$where'";
						break;
					}
				}
				break;
			case 'array':
				foreach ($where as $col_name => $col_value) {
					if (isset($cols[$col_name])) {
						if ($where_sql != '') { $where_sql .= ' AND '; }
						$where_sql .= $db_object->escapeNames($col_name) . " = '" .
							$db_object->escapeStr($col_value) . "'";
					}
				}
				break;
			case 'string':
				$where_sql = trim($where);
				break;
		}
		if ($where_sql == '') { return false; }

		$sql = "UPDATE $scope SET ";
		$first_col = true;
		foreach ($fields_data as $col_name => $col_value) {
			if (isset($cols[$col_name])) {
				$col_obj = $cols[$col_name];
				$err_msg = '';
				$col_value = $col_obj->validateData($col_value, $err_msg);
				if ($err_msg != '') { $this->addError($err_msg, E_USER_WARNING); }
				if (!$first_col) { $sql .= ", "; }
				else { $first_col = false; }
				$sql .= $db_object->escapeNames($col_name) . " = '" .
					$db_object->escapeStr($col_value) . "'";
			}
		}
		$wp = strpos(strtoupper($where_sql), "WHERE");
		if ($wp === false || $wp > 0) { $where_sql = " WHERE " . $where_sql; }
		$sql .= $where_sql;
		if (!is_null($limit) && is_int($limit) && $limit > 0) {
			$sql .= " LIMIT $limit";
		} else if (is_array($limit) && isset($limit[0]) && isset($limit[1]) &&
			is_int($limit[0]) && is_int($limit[1]) && $limit[1] > 0) {
			$sql .= " LIMIT " . $limit[0] . "," . $limit[1];
		}

		return $db_object->query($sql);
	}

	# ###########################################################################

	/**
	* Select data form the database table
	*
	* @param array $fields A list of fields or null
	* @param string $where Plain SQL WHERE conditions
	* @param integer $limit
	* @return resource (MySQL) or false on errors
	*/
	public function select($fields, $where = null, $limit = null, $order_by = null)
	{
		$db_object = $this->database_object;

		$select_sql = '';
		if (is_array($fields) && count($fields) > 0) {
			foreach ($fields as $k => $field) {
				if ($select_sql != '') { $select_sql .= ', '; }
				$select_sql .= $db_object->escapeNames($field);
			}
		}
		if ($select_sql == '' ) { $select_sql = "*"; }

		$table_name = $this->table_name;
		$schema = $this->schema;
		if (!is_null($table_name) && trim($table_name) != '') {
			$schema = $this->schema;
			if (!is_null($schema) && trim($schema) != '') {
				$scope = trim($schema) . '.' . trim($table_name);
			} else {
				$scope = trim($table_name);
			}
		}
		$scope = $db_object->escapeNames($scope);

		$where_sql = '';
		$order_by_sql = '';

		if ($where != null && is_string($where)) {
			$where_sql = trim($where);
			$wp = strpos(strtoupper($where_sql), "WHERE");
			if ($wp === false || $wp > 0) { $where_sql = " WHERE " . $where_sql; }
		}
		if (!is_null($order_by) && trim($order_by) != '') {
			$order_by_sql = trim($order_by);
			$op = strpos(strtoupper($order_by_sql), "ORDER BY");
			if ($wp === false || $wp > 0) { $order_by_sql = " ORDER BY " . $order_by_sql; }
		}

		$sql = trim("SELECT {$select_sql} FROM {$scope} {$where_sql} {$order_by_sql}");

		if (!is_null($limit) && is_int($limit) && $limit > 0) {
			$sql .= " LIMIT $limit";
		} else if (is_array($limit) && isset($limit[0]) && isset($limit[1]) &&
			is_int($limit[0]) && is_int($limit[1]) && $limit[1] > 0) {
			$sql .= " LIMIT " . $limit[0] . "," . $limit[1];
		}

		$this->__sql = $sql;
		$this->__database_result = $db_object->query($sql);

		return $this->__database_result;
	}

	# ###########################################################################

	/**
	* Insert new data into the database table
	*
	* @param array $fields A list of fields and values (field names as array keys)
	* @return resource (MySQL) or false on errors
	*/
	public function insert(array $fields_data)
	{
		if (!is_array($fields_data) || count($fields_data) == 0) { return false; }

		$cols = $this->getCols(false);
		$db_object = $this->database_object;
		$table_name = $this->table_name;
		$schema = $this->schema;
		if (!is_null($table_name) && trim($table_name) != '') {
			$schema = $this->schema;
			if (!is_null($schema) && trim($schema) != '') {
				$scope = trim($schema) . '.' . trim($table_name);
			} else {
				$scope = trim($table_name);
			}
		}
		$scope = $db_object->escapeNames($scope);

		$fields_sql = '';
		$values_sql = '';
		$first_col = true;
		foreach ($fields_data as $col_name => $col_value) {
			if (isset($cols[$col_name])) {
				$col_obj = $cols[$col_name];
				$err_msg = '';
				$col_value = $col_obj->validateData($col_value, $err_msg);
				if ($err_msg != '') { $this->addError($err_msg, E_USER_WARNING); }
				if (!$first_col) {
					$fields_sql .= ", ";
					$values_sql .= ", ";
				} else {
					$first_col = false;
				}
				$fields_sql .= $db_object->escapeNames($col_name);
				$values_sql .= "'" . $db_object->escapeStr($col_value) . "'";
			}
		}

		$sql = "INSERT INTO $scope ($fields_sql) VALUES ($values_sql)";
		return $db_object->query($sql);
	}

	# ###########################################################################

	/**
	* Delete data rows from the database table
	*
	* @param mixed $where Delete SQL condition (can be integer - update by primary key,
	* array - a list of conditions with filed names as array keys,
	* or string - plain SQL conditions)
	* @param integer $limit
	*/

	public function delete($where, $limit = null)
	{
		if (is_null($where)) { return false; }

		$db_object = $this->database_object;
		$table_name = $this->table_name;
		$schema = $this->schema;
		if (!is_null($table_name) && trim($table_name) != '') {
			$schema = $this->schema;
			if (!is_null($schema) && trim($schema) != '') {
				$scope = trim($schema) . '.' . trim($table_name);
			} else {
				$scope = trim($table_name);
			}
		}
		$scope = $db_object->escapeNames($scope);


		$where_sql = '';
		switch (gettype($where)) {
			case 'integer':
				foreach ($cols as $col) {
					if ($col->primary_key) {
						$where_sql = $db_object->escapeNames($col->name) . " = '$where'";
						break;
					}
				}
				break;
			case 'array':
				$cols = $this->getCols(false);
				foreach ($where as $col_name => $col_value) {
					if (isset($cols[$col_name])) {
						if ($where_sql != '') { $where_sql .= ' AND '; }
						$where_sql .= $db_object->escapeNames($col_name) . " = '" .
							$db_object->escapeStr($col_value) . "'";
					}
				}
				break;
			case 'string':
				$where_sql = trim($where);
				break;
		}
		if ($where_sql == '') { return false; }
		$wp = strpos(strtoupper($where_sql), "WHERE");
		if ($wp === false || $wp > 0) { $where_sql = "WHERE " . $where_sql; }

		$sql = "DELETE FROM $scope $where_sql";
		if (is_null($limit) || !is_int($limit)) { $limit = 0; }
		if ($limit > 0) { $sql .= " LIMIT $limit"; }
		return $db_object->query($sql);
	}

}
